package com.sunny.myproject.sales.action;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.sunny.myproject.core.base.action.BaseAction;
import com.sunny.myproject.core.base.util.DateUtil;
import com.sunny.myproject.sales.entity.SalesTicket;
import com.sunny.myproject.sales.service.SalesTicketService;

@Controller
@RequestMapping("/sales/salesTicket")
public class SalesTicketAction extends BaseAction<SalesTicket> {

	@Autowired
	private SalesTicketService service;

	@Override
	@RequestMapping(params = "list")
	public String list(Model model, HttpServletRequest request,
			HttpServletResponse response) {
		super.pager = super.pageStart(model, request);
		super.pager = super.pageEnd(model, service.num(criterions), pager);
		super.returnValue(model, service.find(criterions,
				super.pager.getFirstResults(), super.pager.getMaxResults()));
		return "sales/salesTicketList";
	}

	@Override
	@RequestMapping(params = "add")
	public String add(HttpServletRequest reuqest, HttpServletResponse response) {
		return "sales/salesTicketEdit";
	}

	@Override
	@RequestMapping(params = "edit")
	public String edit(Model model, HttpServletRequest request,
			HttpServletResponse response) {
		super.returnValue(model, service.findById(super.param1(request)));
		return "sales/salesTicketEdit";
	}

	@Override
	@RequestMapping(params = "delete")
	public String delete(HttpServletRequest request,
			HttpServletResponse response) {
		service.deleteByIds(super.param2(request));
		return "redirect:/sales/salesTicket?list";
	}

	@Override
	@RequestMapping(params = "save")
	public @ResponseBody
	String save(HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute SalesTicket t) {
		service.save(t);
		return "sales/salesTicket?list";
	}

	@Override
	@RequestMapping(params = "view")
	public String view(Model model, HttpServletRequest request,
			HttpServletResponse response) {
		super.returnViewValue(model, service.findById(super.param1(request)));
		return "sales/salesTicketEdit";
	}

	/**
	 * 销售记录上传
	 * @param file
	 * @return
	 */
	@RequestMapping(params = "upload")
	public String file(@RequestParam("file") MultipartFile file) {
		try {
			InputStream is = file.getInputStream();

			// 创建xls文件对象
			HSSFWorkbook workbook = new HSSFWorkbook(is);
			HSSFSheet sheet = workbook.getSheetAt(0);// 取出第一个工作表
			// 开始循环遍历行，表头不处理
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				// 获取行对象
				HSSFRow row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				SalesTicket ticket = new SalesTicket();
				ticket.setP_name(row.getCell(0).getStringCellValue());
				ticket.setP_type(row.getCell(1).getStringCellValue());
				ticket.setSaler(row.getCell(2).getStringCellValue());
				ticket.setShop(row.getCell(3).getStringCellValue());
				ticket.setPrice(row.getCell(4).getNumericCellValue());
				ticket.setAmount(Integer
						.parseInt(String.valueOf(
								row.getCell(5).getNumericCellValue()).replace(
								".0", "")));
				ticket.setDate(new Date());

				service.save(ticket);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return "sales/uploadSuccess";
	}
	
	
	/**
	 * 销售前三的金额查询
	 * @param type
	 * @param date
	 * @return
	 * @throws ParseException 
	 */
	public String queryTop3Price(@RequestParam("type")String type,@RequestParam("date")String date,Model model) throws ParseException{
		StringBuffer hql =new StringBuffer("SELECT SUM(price),p_name FROM SalesTicket where 1=1 ");
		
		//日查询
		if(type.equals("0")){
			hql.append("date ="+DateUtil.formateStringDate(date)+" ");
			hql.append("GROUP BY p_name ORDER BY price DESC");
		}else if(type.equals("1")){
			hql.append("date<="+DateUtil.getFirtDateOfMonth(date)+" ");
			hql.append(" and date<="+DateUtil.getEndDateOfMonth(date)+" ");
			hql.append("GROUP BY p_name ORDER BY price DESC");
		}
		@SuppressWarnings("unchecked")
		List<Object[]> list = (List<Object[]>) service.queryHql(hql.toString());
		List<SalesTicket> salesTickets = new ArrayList<SalesTicket>();
		for (Object[] object : list) {
			SalesTicket salesTicket = new SalesTicket();
			salesTicket.setPrice(Double.parseDouble(String.valueOf(object[0])));
			salesTicket.setP_name(String.valueOf(object[1]));
			salesTickets.add(salesTicket);
		}
		this.returnValue(model, salesTickets);
		return "sales/query3Money";
	}
	
	/**
	 * 销售前三的数量查询
	 * @param type
	 * @param date
	 * @return
	 * @throws ParseException 
	 */
	public String queryTop3Amount(@RequestParam("type")String type,@RequestParam("date")String date,Model model) throws ParseException{
	StringBuffer hql =new StringBuffer("SELECT SUM(amount),p_name FROM SalesTicket where 1=1 ");
		
		//日查询
		if(type.equals("0")){
			hql.append("date ="+DateUtil.formateStringDate(date)+" ");
			hql.append("GROUP BY p_name ORDER BY amount DESC");
		}else if(type.equals("1")){
			hql.append("date<="+DateUtil.getFirtDateOfMonth(date)+" ");
			hql.append(" and date<="+DateUtil.getEndDateOfMonth(date)+" ");
			hql.append("GROUP BY p_name ORDER BY amount DESC");
		}
		@SuppressWarnings("unchecked")
		List<Object[]> list = (List<Object[]>) service.queryHql(hql.toString());
		List<SalesTicket> salesTickets = new ArrayList<SalesTicket>();
		for (Object[] object : list) {
			SalesTicket salesTicket = new SalesTicket();
			salesTicket.setPrice(Double.parseDouble(String.valueOf(object[0])));
			salesTicket.setP_name(String.valueOf(object[1]));
			salesTickets.add(salesTicket);
		}
		this.returnValue(model, salesTickets);
		return "sales/query3Amount";
	}
}
